package com.example.teaching_server.service;

import com.example.teaching_server.dto.StudentDTO;
import com.example.teaching_server.entity.StudentStatisticsEntity;
import com.example.teaching_server.entity.TermEntity;
import com.example.teaching_server.repository.StudentStatisticsRepository;
import com.example.teaching_server.repository.TermRepository;
import com.example.teaching_server.repository.GradeRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 学生管理服务
 */
@Slf4j
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class StudentService {

    private final TermRepository termRepository;
    private final GradeRepository gradeRepository;
    private final StudentStatisticsRepository studentStatisticsRepository;
    private final JdbcTemplate jdbcTemplate;

    /**
     * 获取所有学期
     */
    public List<TermEntity> getAllTerms() {
        log.info("获取所有学期");
        return termRepository.findAllByOrderByStartDateDesc();
    }

    /**
     * 获取所有年级
     */
    public List<String> getAllGradeLevels() {
        log.info("获取所有年级");
        return gradeRepository.findAllGradeNames();
    }

    /**
     * 根据条件查询学生
     */
    public List<StudentDTO> getStudentsByCriteria(Long teacherId, Long termId, String gradeLevel) {
        log.info("根据条件查询学生: teacherId={}, termId={}, gradeLevel={}", teacherId, termId, gradeLevel);

        validateTeacherId(teacherId);

        List<StudentDTO> students = getStudentBasicInfo(teacherId, termId, gradeLevel);

        if (students.isEmpty()) {
            log.info("未找到符合条件的学生");
            return students;
        }

        List<Long> studentIds = extractStudentIds(students);
        Long effectiveTermId = getEffectiveTermId(termId);
        Map<Long, StudentStatisticsEntity> statisticsMap = getStudentStatistics(studentIds, effectiveTermId);

        return mergeStatisticsWithStudents(students, statisticsMap);
    }

    /**
     * 获取学生基本信息
     */
    private List<StudentDTO> getStudentBasicInfo(Long teacherId, Long termId, String gradeLevel) {
        log.debug("查询学生基本信息: teacherId={}, termId={}, gradeLevel={}", teacherId, termId, gradeLevel);

        StringBuilder sql = buildStudentQuerySql();
        List<Object> params = buildQueryParams(teacherId, termId, gradeLevel);

        try {
            return executeStudentQuery(sql.toString(), params);
        } catch (Exception e) {
            log.error("查询学生基本信息失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 构建学生查询SQL
     */
    private StringBuilder buildStudentQuerySql() {
        return new StringBuilder(
                "SELECT u.id, u.username, u.name, u.role, u.student_id, " +
                        "g.grade_name as gradeLevel, c.class_name as className " +
                        "FROM users u " +
                        "JOIN classes c ON u.class_id = c.class_id " +
                        "JOIN grades g ON c.grade_id = g.grade_id " +
                        "JOIN teacher_class tc ON c.class_id = tc.class_id " +
                        "WHERE tc.teacher_id = ? " +
                        "AND u.role = 'student' "
        );
    }

    /**
     * 构建查询参数
     */
    private List<Object> buildQueryParams(Long teacherId, Long termId, String gradeLevel) {
        List<Object> params = new ArrayList<>();
        params.add(teacherId);

        if (termId != null) {
            params.add(termId);
        }

        if (gradeLevel != null && !gradeLevel.isEmpty()) {
            params.add(gradeLevel);
        }

        return params;
    }

    /**
     * 执行学生查询
     */
    private List<StudentDTO> executeStudentQuery(String sql, List<Object> params) {
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            StudentDTO dto = new StudentDTO();
            dto.setId(rs.getInt("id"));
            dto.setUsername(rs.getString("username"));
            dto.setName(rs.getString("name"));
            dto.setRole(rs.getString("role"));
            dto.setStudentId(rs.getString("student_id"));
            dto.setGradeLevel(rs.getString("gradeLevel"));
            dto.setClassName(rs.getString("className"));
            return dto;
        }, params.toArray());
    }

    /**
     * 获取有效的学期ID
     */
    private Long getEffectiveTermId(Long termId) {
        if (termId != null) {
            return termId;
        }
        return termRepository.findByIsCurrentTrue()
                .map(term -> term.getId() != null ? term.getId() : 1L)
                .orElse(1L);
    }

    /**
     * 获取学生统计信息
     */
    private Map<Long, StudentStatisticsEntity> getStudentStatistics(List<Long> studentIds, Long termId) {
        log.debug("获取学生统计信息: studentIds={}, termId={}", studentIds, termId);

        List<StudentStatisticsEntity> statistics = studentStatisticsRepository.findByStudentIdsAndTermId(studentIds, termId);
        return statistics.stream()
                .collect(Collectors.toMap(
                        stat -> stat.getStudentId() != null ? stat.getStudentId() : 0L,
                        stat -> stat
                ));
    }

    /**
     * 合并统计信息到学生数据
     */
    private List<StudentDTO> mergeStatisticsWithStudents(List<StudentDTO> students,
                                                         Map<Long, StudentStatisticsEntity> statisticsMap) {
        return students.stream()
                .map(student -> {
                    StudentStatisticsEntity stats = statisticsMap.get(Long.valueOf(student.getId()));
                    setStudentStatistics(student, stats);
                    return student;
                })
                .collect(Collectors.toList());
    }

    /**
     * 设置学生统计信息
     */
    private void setStudentStatistics(StudentDTO student, StudentStatisticsEntity stats) {
        if (stats != null) {
            student.setAverageScore(stats.getAverageScore());
            student.setAttendanceRate(stats.getAttendanceRate());
            student.setHonorEvaluation(stats.getHonorEvaluation());
        } else {
            student.setAverageScore(0.0);
            student.setAttendanceRate(0.0);
            student.setHonorEvaluation("average");
        }
    }

    /**
     * 提取学生ID列表
     */
    private List<Long> extractStudentIds(List<StudentDTO> students) {
        return students.stream()
                .map(student -> Long.valueOf(student.getId()))
                .collect(Collectors.toList());
    }

    /**
     * 验证教师ID
     */
    private void validateTeacherId(Long teacherId) {
        if (teacherId == null || teacherId <= 0) {
            throw new IllegalArgumentException("教师ID不能为空且必须大于0");
        }
    }
}
